package com.g2room.driving.grap.life;

import java.io.IOException;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.jsoup.nodes.Document;
import org.jsoup.nodes.Element;
import org.jsoup.select.Elements;

import com.alibaba.fastjson.JSON;
import com.g2room.driving.grap.GenericGrap;
import com.g2room.driving.grap.life.model.po.ComShopComment;
import com.g2room.driving.grap.life.model.po.LifeNews;
import com.g2room.driving.grap.life.model.po.SchSchool;
import com.g2room.driving.grap.life.model.po.SchSchoolImg;
import com.g2room.driving.util.Base64Util;
import com.g2room.myuall.grap.ky.model.po.GrapErr;
import com.g2room.myuall.tools.ImageUtil;
import com.g2room.myuall.util.DbUtil;
import com.g2room.myuall.util.StringUtil;

public class GrapXuexiaoData extends GenericGrapNews {
	public static String table = "sch_school";
	private static  String yddBaseUrl = "http://www.yikexun.cn";
	private static  String source = "挖段子";
	private static  Map<String, Integer> errUrls = new HashMap<String, Integer>();
	private static  List<String> detailUrls = new ArrayList<String>();
	private  List<String> listUrls = new ArrayList<String>();
	private static List<SchSchool> schools = new ArrayList<SchSchool>();
	
	public static void main(String[] args) throws Exception{
		GrapXuexiaoData gl = new GrapXuexiaoData();
		getSourceUrl();
		for(int i = 1; i< 248; i++) {
			System.out.println("开始抓="+i);
			gl.get51sxueList("http://xuexiao.51sxue.com/slist/?o=&t=&areaCodeS=50&level=&sp=&score=&order=&areaS=%D6%D8%C7%EC%CA%D0&searchKey=&page=" + i);
		}
		
//		// 抓取图片
//		getSchools();
//		for(SchSchool ss : schools) {
////			gl.getSchoolImg(ss);
//			gl.getSchoolComments(ss);
//			gl.updateContent(ss.getCode(), ss.getId());
// 		}
	}

//	public void getSchoolNewsList(SchSchool ss) {
//		Document doc = getDoc("http://xuexiao.51sxue.com/news/id_"+ss.getCode()+".html", "10");
//		if(doc == null) return;
//		Elements els= doc.select(".school_dt ul li a");
//		if(null == els || els.isEmpty()) return;
//		List<String> urls = new ArrayList<String>();
//		for(Element el : els) {
//			urls.add(el.attr("href"));
//		}
//		List<LifeNews> lns = new ArrayList<LifeNews>();
//		for(String url : urls) {
//			LifeNews ln = getSchoolNews(url);
//			if(null == ln) lns.add(ln);
//		}
//		if(lns.size() > 0) insert2NewsDb(lns);
//	}
//
//	public LifeNews getSchoolNews(String url) {
//		Document doc = getDoc(url, "11");
//		if(doc == null) return null;
//		Elements els= doc.select(".school_dt ul li a");
//		if(null == els || els.isEmpty()) return null;
//		List<String> urls = new ArrayList<String>();
//		for(String url : urls) {
//			// 获取头像和昵称
//			ComShopComment csc = new ComShopComment();
//			csc.setHead("http://img.51sxue.com/newtpl/images/face/2.gif");
//			csc.setUsername("游客");
//			
//			Elements tels = e.select(".school_pl_det .school_pl_main .school_pl_wz");
//			if(null != tels && !tels.isEmpty()) {
//				csc.setContent(tels.first().text());
//			}
//			tels = e.select(".school_pl_det .school_pl_btn .school_pl_hueiyh");
//			if(null != tels && !tels.isEmpty()) {
//				csc.setCommentTime(tels.first().text());
//			}
//			ssis.add(csc);
//		}
//		insertSchoolComment(ssis);
//	}
	
	public void getSchoolComments(SchSchool ss) {
		Document doc = getDoc("http://pinglun.51sxue.com/school/comment/id_"+ss.getCode()+".html", "8");
		if(doc == null) return;
		Elements els= doc.select("#shuchu ul");
		if(null == els || els.isEmpty()) return;
		List<ComShopComment> ssis = new ArrayList<ComShopComment>();
		for(Element e : els) {
			// 获取头像和昵称
			ComShopComment csc = new ComShopComment();
			csc.setHead("http://img.51sxue.com/newtpl/images/face/2.gif");
			csc.setUsername("游客");
			csc.setSchoolId(ss.getId());
			csc.setSchoolName(ss.getName());
			Elements tels = e.select(".school_pl_det .school_pl_main .school_pl_wz");
			if(null != tels && !tels.isEmpty()) {
				csc.setContent(tels.first().text());
			}
			tels = e.select(".school_pl_det .school_pl_btn .school_pl_hueiyh");
			if(null != tels && !tels.isEmpty()) {
				csc.setCommentTime(tels.first().text());
			}
			ssis.add(csc);
		}
		insertSchoolComment(ssis);
	}
	
	public void insertSchoolComment(List<ComShopComment> comments) {
		// http://pinglun.51sxue.com/school/comment/id_7605.html
		String sql = "insert into sch_comment (head, username, content, comment_time, school_name, school_id) values(?,?,?,?,?,?)";
		PreparedStatement ps = null;
		try {
			ps = DbUtil.getConn(db).prepareStatement(sql);
			for(ComShopComment cc : comments) {
				ps.setString(1, cc.getHead());
				ps.setString(2, cc.getUsername());
				ps.setString(3, cc.getContent());
				ps.setString(4, cc.getCommentTime());
				ps.setString(5, cc.getSchoolName());
				ps.setInt(6, cc.getSchoolId());
				ps.addBatch();
			}
			ps.executeBatch();	
		} catch (Exception e) {
			e.printStackTrace();
			GrapErr ge = new  GrapErr();
			ge.setType("6");
			ge.setContent(JSON.toJSONString(comments));
			ge.setDescription("获取DB错误");
			insert2ErrDb(ge);
			
		} finally {
			if(null != ps) {
				try {
					ps.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
	}
	
	public void insertSchoolImg(List<SchSchoolImg> artis, String tb) {
		String sql = "insert into "+tb+"(img_url,school_code,school_id,school_name) values(?,?,?,?)";
		PreparedStatement ps = null;
		try {
			ps = DbUtil.getConn(db).prepareStatement(sql);
			for(SchSchoolImg cc : artis) {
				ps.setString(1, cc.getImgUrl());
				ps.setString(2, cc.getSchoolCode());
				ps.setInt(3, cc.getSchoolId());
				ps.setString(4, cc.getSchoolName());
				ps.addBatch();
			}
			ps.executeBatch();	
		} catch (Exception e) {
			e.printStackTrace();
			GrapErr ge = new  GrapErr();
			ge.setType("5");
			ge.setContent(JSON.toJSONString(artis));
			ge.setDescription("获取DB错误");
			insert2ErrDb(ge);
		
		} finally {
			if(null != ps) {
				try {
					ps.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
	}
	
	public void getSchoolImg(SchSchool ss) {
		Document doc = getDoc("http://xuexiao.51sxue.com/photo/index/s_"+ss.getCode()+".html", "8");
		if(doc == null) return;
		Elements els= doc.select(".photo_img ol dl a img");
		if(null == els || els.isEmpty()) return;
		List<SchSchoolImg> ssis = new ArrayList<SchSchoolImg>();
		for(Element e : els) {
			if(null != e.attr("src")) {
				SchSchoolImg ssi = new SchSchoolImg();
				ssi.setSchoolCode(ss.getCode());
				ssi.setSchoolId(ss.getId());
				ssi.setSchoolName(ss.getName());
				ssi.setImgUrl(e.attr("src").replace("_s", ""));
				ssis.add(ssi);
			}
		}
		insertSchoolImg(ssis, "sch_school_img");
	}
	
	public String updateContent(String code, int id) {
		Document doc = getDoc("http://xuexiao.51sxue.com/zhaosheng/id_"+code+".html", "7");
		if(doc == null) return null;
		Elements els= doc.select("#nr_main .nr_l .nr_m");
		if(null == els || els.isEmpty()||StringUtil.isBlank(els.text())) return null;
		
		els.select("img").remove();
		els.select("div").remove();
		updateDb("enrollment", els.first().html(), id);
		return els.html();
	}
	
	
	public Document getDoc(String url, String errCode) {
		// 获取分页信息
		Document doc = null;
		try {
			doc = GenericGrap.getDocByIp(url);
		} catch (IOException e) {
			e.printStackTrace();
			System.out.println(url + "======" + e.getMessage());
			GrapErr ge = new  GrapErr();
			ge.setUrl(url);
			ge.setType(errCode);
			ge.setDescription("错误："+e.getMessage());
			insert2ErrDb(ge);
		}
		return doc;
	}

	/**
	 * 获取一点点列表
	 */
	public void get51sxueList(String url) {
		// 获取分页信息
		Document doc = getDoc(url, "2");
		if(doc == null) return;
		Elements els = doc.select(".school_main div div.school_t_con");
		List<SchSchool> sses = new ArrayList<SchSchool>();
		if(null != els && !els.isEmpty()) {
			for(Element el : els) {
				SchSchool ss = new  SchSchool();
				// 封面
				Elements tels = el.select(".school_m_img a img");
				if(null != els && !els.isEmpty() && null != tels.first()) {
					ss.setCover(tels.first().attr("src"));
					if(StringUtil.isNotBlank(ss.getCover())) {
						getCover(ss.getCover().replace("_s.", "."), ss, null);
//						ss.setCover(ss.getCover().replace("_s.", "."));
					}
				}
				// 标题
				tels = el.select(".school_m_main li h3 a");
				if(null != tels && !tels.isEmpty() && !detailUrls.contains(tels.first().attr("href"))) {
					ss.setSourceUrl(tels.first().attr("href"));
					detailUrls.add(ss.getSourceUrl());
				} else {
					continue;
				}

				// 属性和类型
				tels = el.select(".school_m_main li");
				if(null != tels && !tels.isEmpty()) {
					for(Element e : tels) {
						if(e.text().contains("属性")) {
							Elements ttels = e.select("b");
							if(null != ttels && !ttels.isEmpty()) {
								ss.setAttr(ttels.first().text());
							}
						} else if(e.text().contains("性质") || e.text().contains("类型")) {
							Elements ttels = e.select("ol");
							if(null != ttels && !ttels.isEmpty()) {
								for(Element te : ttels) {
									if(te.text().contains("性质")) {
										ss.setNatureName(te.text().replace("性质:", ""));
									} else if(te.text().contains("类型")) {
										ss.setTypeName(te.text().replace("类型:", ""));
									}
								}
							}
						}
					}
				}
				sses.add(ss);
			}
		}
		List<SchSchool> sches = new ArrayList<SchSchool>();
        for(SchSchool ss : sses) {
        	sleep();
        	try{
        	SchSchool art = get51sxueDetail(ss);
        	if(null != art) sches.add(art);
        	} catch (Exception e) {
    			e.printStackTrace();
    			System.out.println(url + "======" + e.getMessage());
    			GrapErr ge = new  GrapErr();
    			ge.setUrl(url);
    			ge.setType("5");
    			ge.setContent(JSON.toJSONString(ss));
    			ge.setDescription("错误："+e.getMessage());
    			insert2ErrDb(ge);
    		}
        }
        if(sches.size() > 0) insertArtiDb(sches, "sch_school");
	}
	

	public SchSchool get51sxueDetail(SchSchool ss) {
		// 获取分页信息
		Document doc = getDoc(ss.getSourceUrl(), "3");
		if(doc == null) return null;
		Elements els = doc.select(".mainb li");
		// 获取标题和区域
		if(null != els && !els.isEmpty()) {
			if(els.size() == 6) {
				ss.setName(els.get(5).text());
				ss.setCityName(els.get(4).text());
				ss.setProvinceName("重庆市");
			} else if(els.size() == 5) {
				ss.setName(els.get(4).text());
				ss.setCityName(els.get(3).text());
				ss.setProvinceName("重庆市");
			} else if(els.size() == 4) {
				ss.setName(els.get(3).text());
				ss.setCityName(els.get(2).text());
				ss.setProvinceName("重庆市");
			} else if(els.size() == 3) {
				ss.setName(els.get(2).text());
				ss.setCityName(els.get(1).text());
				ss.setProvinceName("重庆市");
			} else {
				System.out.println(ss.getSourceUrl());
			}
		}
		// 得分
		els = doc.select(".school_con_l .school_def ul li");
		if(null != els && !els.isEmpty()) {
			String clz = els.get(els.size() - 1).attr("class");
			if(StringUtil.isNotBlank(clz)) {
				ss.setScoreOverall(clz.replace("school_fs_", ""));
			}
		}
		// 得分
		els = doc.select(".school_con_l .school_fx ul li");
		if(null != els && !els.isEmpty()) {
			for(Element el : els) {
				String clz = el.attr("class");
				if(StringUtil.isBlank(clz)) continue;
				
				if(clz.equalsIgnoreCase("school_fx_1")) {
					ss.setScoreScale(el.text());
				} else if(clz.equalsIgnoreCase("school_fx_2")) {
					ss.setScoreEnv(el.text());
				} else if(clz.equalsIgnoreCase("school_fx_3")) {
					ss.setScoreCharge(el.text());
				} else if(clz.equalsIgnoreCase("school_fx_4")) {
					ss.setScoreEquipment(el.text());
				} else if(clz.equalsIgnoreCase("school_fx_5")) {
					ss.setScoreHealth(el.text());
				} else if(clz.equalsIgnoreCase("school_fx_6")) {
					ss.setScoreEducation(el.text());
				} else if(clz.equalsIgnoreCase("school_fx_7")) {
					ss.setScoreProfile(el.text());
				} else if(clz.equalsIgnoreCase("school_fx_8")) {
					ss.setScoreFaculty(el.text());
				} else if(clz.equalsIgnoreCase("school_fx_9")) {
					ss.setScoreFood(el.text());
				} else if(clz.equalsIgnoreCase("school_fx_10")) {
					ss.setScoreSociology(el.text());
				}
			}
		}
		
		Element el = doc.getElementById("phone");
		if(null != el) {
			ss.setPhone(el.text().replace("电话:", ""));
		}
		el = doc.getElementById("address");
		if(null != el) {
			ss.setAddress(el.text().replace("地址:", ""));
		}
		el = doc.getElementById("wst");
		if(null != el) {
			ss.setWebsite(Base64Util.decode(el.attr("value")));
		}
		// 获取其他信息
		els= doc.select("div.school_tit");
		if(null != els && !els.isEmpty()) {
			for(Element e : els) {
				Elements tels = e.select(".school_tit_top .school_atr");
				if(tels.text().contains("对口升学")) {
					tels = e.select(".school_jies .school_t");
					if(null != tels && !tels.isEmpty()) {
						ss.setEntranceSchoolName(tels.first().text());
					}
				} else if(tels.text().contains("点评热点")) {
					tels = e.select(".school_jies .school_t");
					if(null != tels && !tels.isEmpty()) {
						ss.setCommentHot(tels.first().text());
					}
				} 
			}
		}
		// 收费
		els = doc.select(".school_fy_kc .school_sf .school_sf_main");
		if(null != els && !els.isEmpty()) {
			ss.setTuition(els.first().text());
		}
		// 课程
		els = doc.select(".school_fy_kc .school_kc .school_kc_main .school_y_kc");
		if(null != els && !els.isEmpty()) {
			ss.setCourse(els.first().text());
		}
		// 设置code
		ss.setCode(ss.getSourceUrl().substring(ss.getSourceUrl().lastIndexOf("_")+ 1).replace(".html", ""));
		// 介绍
		ss.setDescription(getContent(ss.getCode()));
		ss.setSource("搜学网");
		return ss;
	}
	
	public String getContent(String code) {
		Document doc = getDoc("http://xuexiao.51sxue.com/content/id_"+code+".html", "6");
		if(doc == null) return null;
		Elements els= doc.select("#nr_main .nr_l .nr_m");
		if(null == els || els.isEmpty()) return null;
//		els.select("img").remove();
//		StringBuffer content = new StringBuffer();
		els.select("img").remove();
		els.select("div").remove();
//		for(Element e : els) {
//			if(e.tagName().equalsIgnoreCase("div") || e.tagName().equalsIgnoreCase("img") ) {
//				e.remove();
//			} else {
//				content.append(e.outerHtml());
//			}
//		}
		return els.html();
	}
	
	public void insertArtiDb(List<SchSchool> artis, String tb) {
		String sql = "insert into "+tb+"(cover_height,cover_width,score_health,source,course,code,score_education,address,entrance_school_name,score_env,name,score_profile,tuition,score_scale,type_name,city_name,province_name,score_overall,description,nature_name,cover,score_charge,score_equipment,source_url,phone,score_sociology,attr,createtime,score_food,website,score_faculty,comment_hot) values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
		PreparedStatement ps = null;
		try {
			ps = DbUtil.getConn(db).prepareStatement(sql);
			for(SchSchool cc : artis) {
				
				ps.setInt(1, cc.getCoverHeight());
				ps.setInt(2, cc.getCoverWidth());
				ps.setString(3, cc.getScoreHealth());
				ps.setString(4, cc.getSource());
				ps.setString(5, cc.getCourse());
				ps.setString(6, cc.getCode());
				ps.setString(7, cc.getScoreEducation());
				ps.setString(8, cc.getAddress());
				ps.setString(9, cc.getEntranceSchoolName());
				ps.setString(10, cc.getScoreEnv());
				ps.setString(11, cc.getName());
				ps.setString(12, cc.getScoreProfile());
				ps.setString(13, cc.getTuition());
				ps.setString(14, cc.getScoreScale());
				ps.setString(15, cc.getTypeName());
				ps.setString(16, cc.getCityName());
				ps.setString(17, cc.getProvinceName());
				ps.setString(18, cc.getScoreOverall());
				ps.setString(19, cc.getDescription());
				ps.setString(20, cc.getNatureName());
				ps.setString(21, cc.getCover());
				ps.setString(22, cc.getScoreCharge());
				ps.setString(23, cc.getScoreEquipment());
				ps.setString(24, cc.getSourceUrl());
				ps.setString(25, cc.getPhone());
				ps.setString(26, cc.getScoreSociology());
				ps.setString(27, cc.getAttr());
				ps.setString(28, "2017-03-13 16:26:01");
				ps.setString(29, cc.getScoreFood());
				ps.setString(30, cc.getWebsite());
				ps.setString(31, cc.getScoreFaculty());
				ps.setString(32, cc.getCommentHot());
				ps.addBatch();
			}
			ps.executeBatch();	
		} catch (Exception e) {
			e.printStackTrace();
			GrapErr ge = new  GrapErr();
			ge.setType("5");
			ge.setContent(JSON.toJSONString(artis));
			ge.setDescription("获取DB错误");
			insert2ErrDb(ge);
		
		} finally {
			if(null != ps) {
				try {
					ps.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
	}

	public void insert2ErrDb(GrapErr ge) {
		PreparedStatement grapPs = null;
		try {
			grapPs = DbUtil.getConn(db).prepareStatement("insert into grap_err(description,url,content,type) values(?,?,?,?)");
			grapPs.setString(1, ge.getDescription());
			grapPs.setString(2, ge.getUrl());
			grapPs.setString(3, ge.getContent());
			grapPs.setString(4, ge.getType());
			grapPs.execute();
			grapPs.close();
		} catch(Exception e) {
			e.printStackTrace();
		} finally {
			if(null != grapPs) {
				try {
					grapPs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
	}
	

	public void updateDb(String field, String v, int id) {
		PreparedStatement grapPs = null;
		try {
			grapPs = DbUtil.getConn(db).prepareStatement("update "+table+" set "+field+"=? where id=?");
			grapPs.setString(1, v);
			grapPs.setInt(2, id);
			grapPs.execute();
			grapPs.close();
		} catch(Exception e) {
			e.printStackTrace();
		} finally {
			if(null != grapPs) {
				try {
					grapPs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
	}

	public static void getSourceUrl() {
		try {
			Statement statement = DbUtil.getConn(db).createStatement();
			String sql = "select source_url from sch_school";
			ResultSet rs = statement.executeQuery(sql);
			while(rs.next()) {
				detailUrls.add(rs.getString("source_url"));
			}
			rs.close();
			statement.close();
		} catch(Exception e) {
			e.printStackTrace();
		}
	}

	public static void getSchools() {
		try {
			Statement statement = DbUtil.getConn(db).createStatement();
			String sql = "select id,code,name from sch_school";
			ResultSet rs = statement.executeQuery(sql);
			while(rs.next()) {
				SchSchool sc = new  SchSchool();
				sc.setId(rs.getInt("id"));
				sc.setCode(rs.getString("code"));
				sc.setName(rs.getString("name"));
				schools.add(sc);
			}
			rs.close();
			statement.close();
		} catch(Exception e) {
			e.printStackTrace();
		}
	}

	public static void getErrUrl() {
		try {
			Statement statement = DbUtil.getConn(db).createStatement();
			String sql = "select url, type from grap_err where type>20";
			ResultSet rs = statement.executeQuery(sql);
			while(rs.next()) {
				errUrls.put(rs.getString("url"), rs.getInt("type"));
			}
			rs.close();
			statement.close();
		} catch(Exception e) {
			e.printStackTrace();
		}
	}

	public void sleep() {
    	try {
    		Thread.sleep(Integer.parseInt(StringUtil.genRandomNum(1)) * 100);
    	} catch (Exception e) {
    	}
	}
	

	public void getCover(String src, SchSchool ln, String baseUrl) {
		// 判断是否有图片，有则获取第一张图片作为封面
		if(null == baseUrl || src.startsWith("http:")) {
			ln.setCover(src);
		} else {
			ln.setCover(baseUrl + (src.startsWith("/") ? src : "/"+src));
		}
		// 获取宽度高度
		Map<Integer, Integer> map = ImageUtil.getImgWidth(ln.getCover());
		if(null != map) {
			for(int key : map.keySet()) {
				ln.setCoverWidth(key);
				ln.setCoverHeight(map.get(key));
			}
		} else {
			ln.setCoverWidth(0);
			ln.setCoverHeight(0);
		}
	}
}
